﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using Active.Modules.Social;
using Microsoft.ApplicationBlocks.Data;
using System.Data;

namespace DotNetNuke.Modules.ActiveSocialMigrator.Data
{
    public class GroupsDB : Connection
    {
        internal void MoveForums(int ActiveSocialGroupId, int RoleId) {
            string sqlText = "SELECT * FROM " + databaseOwner + objectQualifier + "activesocial_Groups_Forums WHERE GroupId = " + ActiveSocialGroupId.ToString();
            using (IDataReader dr = SqlHelper.ExecuteReader(connectionString, CommandType.Text, sqlText)) {
                while (dr.Read()) {
                    int ForumId = (int)dr["ForumId"];
                    string ForumUpdate = "UPDATE " + databaseOwner + objectQualifier + "activeforums_Forums SET SocialGroupId = " + RoleId + " WHERE ForumId = " + ForumId;
                    SqlHelper.ExecuteNonQuery(connectionString, CommandType.Text, ForumUpdate);
                }
                dr.Close();
            }
            
        }
        internal void CreateGroupMap(int ActiveSocialGroupId, int RoleId)
        {
            string sqlText = "INSERT INTO " + databaseOwner + objectQualifier + "asmigrator_GroupMap (ActiveSocialGroupId, RoleId) Values ({0}, {1})";
            sqlText = String.Format(sqlText, ActiveSocialGroupId, RoleId);
            SqlHelper.ExecuteNonQuery(connectionString, CommandType.Text, sqlText);
        }
        internal int GetGroupId(int ActiveSocialGroupId)
        {
            string sqlText = "SELECT ISNULL(RoleId,-1) as RoleId FROM " + databaseOwner + objectQualifier + "asmigrator_GroupMap WHERE ActiveSocialGroupId = {0}";
            sqlText = String.Format(sqlText, ActiveSocialGroupId);
            return (int)SqlHelper.ExecuteScalar(connectionString, CommandType.Text, sqlText);
        }
        public DataTable Groups_Members_List(int GroupId, int PortalId, int MemberStatus, int RowIndex, int MaxRows, bool Random)
        {
            DataSet ds = SqlHelper.ExecuteDataset(connectionString, databaseOwner + objectQualifier + "activesocial_Groups_Members_List", GroupId, PortalId, MemberStatus, RowIndex, MaxRows, Random);
            if (ds.Tables.Count > 0)
            {
                return ds.Tables[0];
            } else
            {
                return null;
            }
        }
        public GroupInfo Group_Get(int GroupId, int PortalId)
        {
            GroupInfo gi = null;
            try
            {
                IDataReader dr = SqlHelper.ExecuteReader(connectionString, dbPrefix + "Groups_Get", GroupId, PortalId, -1);
                while (dr.Read())
                {
                    gi = FillGroupInfo(dr, false);
                }
                if ((gi != null))
                {
                    dr.NextResult();
                    while (dr.Read())
                    {
                        gi.ForumIds += dr["ForumId"].ToString() + ";";
                    }
                    if (!(gi.ForumIds == string.Empty))
                    {
                        if (gi.ForumIds.LastIndexOf(";") == gi.ForumIds.Length - 1)
                        {
                            gi.ForumIds = gi.ForumIds.Substring(0, gi.ForumIds.Length - 1);
                        }
                    }

                }
                if (!dr.IsClosed)
                {
                    dr.Close();
                }
                if ((gi != null))
                {
                    //Data.Categories db = new Data.Categories();
                    //gi.Categories = new List<CategoryItem>();
                    //gi.Categories = db.Category_ListByItemId(gi.PortalId, gi.GroupId, 1);
                    UserController uc = new UserController();
                    gi.GroupOwner = uc.GetUser(PortalId, gi.OwnerId);
                }

            } catch (Exception ex)
            {
                Exceptions.LogException(ex);
            }
            return gi;

        }
        public List<Active.Modules.Social.GroupInfo> Groups_List(int PortalId, int RowIndex, int MaxRows, int StatusId, int UserId, bool Random, string Alpha, string Query, int GroupTypeId, string SortField,
int SortType, int CategoryId)
        {
            GroupInfo grpInfo = null;
            List<GroupInfo> groupList = new List<GroupInfo>();
            try
            {
                using (IDataReader dr = SqlHelper.ExecuteReader(connectionString, databaseOwner + objectQualifier + "activesocial_Groups_List", PortalId, RowIndex, MaxRows, StatusId, UserId, Random, Alpha, Query, GroupTypeId, SortField, SortType, CategoryId))
                {
                    while (dr.Read())
                    {
                        grpInfo = new GroupInfo();
                        grpInfo = FillGroupInfo(dr, true);
                        groupList.Add(grpInfo);

                    }
                    dr.Close();
                }

            } catch (Exception ex)
            {
                Exceptions.LogException(ex);

            }
            return groupList;
        }
        private GroupInfo FillGroupInfo(IDataRecord dr, bool IsForList)
        {
            GroupInfo gi = new GroupInfo();

            {
                gi.GroupId = Convert.ToInt32(dr["GroupId"].ToString());
                gi.PortalId = Convert.ToInt32(dr["PortalId"].ToString());
                //.ModuleId = ModuleId
                gi.GroupIcon = dr["GroupIcon"].ToString();
                gi.GroupName = dr["GroupName"].ToString();
                gi.GroupDescription = dr["GroupDescription"].ToString();
                gi.GroupText = dr["GroupText"].ToString();
                gi.Street = dr["Street"].ToString();
                gi.City = dr["City"].ToString();
                gi.Region = dr["Region"].ToString();
                gi.Country = dr["Country"].ToString();
                gi.PostalCode = dr["PostalCode"].ToString();
                gi.WebSite = dr["WebSite"].ToString();
                gi.AccessType = (GroupAccessTypes)Convert.ToInt32(dr["AccessType"].ToString());
                gi.DateCreated = Convert.ToDateTime(dr["DateCreated"].ToString());
                gi.DateUpdated = Convert.ToDateTime(dr["DateUpdated"].ToString());
                gi.OwnerId = Convert.ToInt32(dr["OwnerId"].ToString());
                gi.Featured = Convert.ToBoolean(dr["Featured"].ToString());
                gi.LinkedRoles = dr["LinkedRoles"].ToString();
                gi.MemberCount = Convert.ToInt32(dr["MemberCount"].ToString());
                gi.ForumIds = string.Empty;
                gi.StatusId = Convert.ToInt32(dr["StatusId"].ToString());
                gi.ParentForumId = Convert.ToInt32(dr["ParentForumId"]);
                gi.CurrentUserMemberStatus = Convert.ToInt32(dr["MemberStatus"]);
                gi.GroupTypeId = Convert.ToInt32(dr["GroupTypeId"].ToString());
                if (!string.IsNullOrEmpty(dr["Stats"].ToString()))
                {
                    gi.Stats = dr["Stats"].ToString();
                }
                if (!string.IsNullOrEmpty(dr["GroupStatus"].ToString()))
                {
                    gi.GroupStatus = dr["GroupStatus"].ToString();
                    gi.GroupStatusDate = Convert.ToDateTime(dr["GroupStatusDate"].ToString());
                }
                gi.VanityName = dr["VanityName"].ToString();
                if (!string.IsNullOrEmpty(dr["GroupData"].ToString()))
                {
                    gi.GroupData = dr["GroupData"].ToString();
                }
                if (IsForList)
                {
                    gi.GroupCount = Convert.ToInt32(dr["GroupCount"].ToString());
                } else
                {
                    gi.DisplayName = dr["DisplayName"].ToString();
                    gi.Username = dr["Username"].ToString();
                    gi.FullName = dr["FullName"].ToString();
                    gi.DefaultAdminRoleId = Convert.ToInt32(dr["DefaultAdminRoleId"]);
                    gi.DefaultMemberRoleId = Convert.ToInt32(dr["DefaultMemberRoleId"]);
                    gi.TwitterEnabled = bool.Parse(dr["TwitterEnabled"].ToString());
                    gi.TwitterToken = dr["TwitterToken"].ToString();
                    gi.TwitterTokenSecret = dr["TwitterTokenSecret"].ToString();
                    gi.CurrentUserSubscriptions = dr["MemberSubscriptions"].ToString();
                }
            }
            return gi;
        }
    }

}